package com.kamikaze;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class WTFteater {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			String user_id = "0001";
			String url = "jdbc:mysql://j.snpy.org/j2?user=team2&password=kamikaze&useUnicode=true&characterEncoding=UTF-8";
			String attendance ="SELECT s.user_id,s.shift_number,s.shift_date,wt.wt_number,wt.on_time,wt.off_time,cd.clock_number,cd.clock_in,cd.clock_out,s.attendance_status , l.leaved_number,l.leave_number " +
					"FROM `shift`s left outer join clockdata cd on s.shift_number=cd.shift_number left outer join leavedata l on l.shift_number = s.shift_number,worktypes wt " +
					"where s.wt_number = wt.wt_number and s.user_id = ? and DATE_FORMAT( s.shift_date,'%Y%m' )= ? and s.shift_date<=?";
			String msalary = "select msalary from month_salary where user_id = ? and month = ?";
			String intosal = "UPDATE  `j2`.`month_salary` SET `msalary` =  ? WHERE  `user_id`=? and `month` =?";
			
			java.util.Date now = new java.util.Date(); //取得現在時間
			SimpleDateFormat ym = new SimpleDateFormat("yyyyMM");
			String yearm = ym.format(now);
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

			
			Class.forName("com.mysql.jdbc.Driver");
			Connection con01= DriverManager.getConnection(url);
			PreparedStatement pstat01 = con01.prepareStatement(attendance);
			pstat01.setString(1, user_id);
			pstat01.setInt(2, 201209);
			pstat01.setInt(3, 20120917);
			ResultSet rs01 = pstat01.executeQuery();
			
			Class.forName("com.mysql.jdbc.Driver");

			
			while(rs01.next()){
				int dif = 0; //算薪水的類型別
				int status = 0;
				int diff=0;
				int diff_mins=0;
				int diff_on=0;
				int diff_off=0;
				
				Connection con02= DriverManager.getConnection(url);
				PreparedStatement pstat02 = con02.prepareStatement(msalary);
				pstat02.setString(1, user_id);
				pstat02.setString(2, yearm);
				ResultSet rs02 = pstat02.executeQuery();
				rs02.next();
				double salary = rs02.getInt("msalary"); //拿本薪
				

				
				
				Date clock_in=rs01.getTimestamp("clock_in");
				System.out.println("clock_in="+ clock_in);
				Date clock_out=rs01.getTimestamp("clock_out");
				System.out.println("clock_out="+clock_out);
//				Time in_time=rs01.getTime("clock_in");
//				System.out.println("in_time"+ in_time);
//				Time out_time=rs01.getTime("clock_out");
//				System.out.println("in_time"+ out_time);
				
				
				
				
				
				
				String on_time=rs01.getString("on_time");
				System.out.println("on_time="+on_time);
				String off_time=rs01.getString("off_time");
				System.out.println("off_time="+off_time);
				String shift_date=rs01.getString("shift_date");
				System.out.println("shift_date="+shift_date);
				
				String shift_on_time = shift_date+" "+on_time;
					Date on_time_date = sdf.parse(shift_on_time);
					//System.out.println("shift_on_time="+shift_on_time);
				System.out.println("on_time_date"+on_time_date);
				String shift_off_time = shift_date+" "+off_time;
				//	System.out.println("shift_off_time="+shift_off_time);
					Date off_time_date = sdf.parse(shift_off_time);
				//	 diff = (int) (clock_in.getTime()-on_time_date.getTime());
				//	System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
				//	 diff_mins = (diff/(1000*60));
					
					
					
					
					
				System.out.println("off_time_date="+off_time_date);
				
				
				
			//	Date shift_date = rs01.getDate("shift_date");
				int shift_number = rs01.getInt("shift_number");
				int att_status = rs01.getInt("attendance_status");//拿status
				System.out.print("attendance_status  "+att_status);
				int leaved_number = rs01.getInt("leaved_number");
				System.out.print("leaved_number="+leaved_number);
				int leave_number = rs01.getInt("leave_number");
				System.out.print("假別="+leave_number);
					int wt_number = rs01.getInt("wt_number");
				if(att_status == 1 ){
					if(leaved_number == 0){dif = 10; } //遲到沒請假
					else{
						if(leaved_number == 1){dif = 11;}//遲到請病假
						if(leaved_number == 2){dif = 12;}//遲到請事假
						if(leaved_number == 3){dif = 13;}//遲到請公假
						if(leaved_number == 4){dif = 14;}//遲到請補休假

					}
				}
				if(att_status == 2 ){
					if(leaved_number == 0){dif = 20; } //早退沒請假
					else{
						if(leaved_number == 1){dif = 21;}//早退請病假
						if(leaved_number == 2){dif = 22;}//早退請事假
						if(leaved_number == 3){dif = 23;}//早退請公假
						if(leaved_number == 4){dif = 24;}//早退請補休假

					}
				}
				if(att_status == 4 ){
					if(leaved_number == 0){dif =40 ; } //曠職沒請假
					else{
						if(leaved_number == 1){dif = 41;}//曠職請病假
						if(leaved_number == 2){dif = 42;}//曠職請事假
						if(leaved_number == 3){dif = 43;}//曠職請公假
						if(leaved_number == 4){dif = 44;}//曠職請補休假

					}
				}	
				if(att_status == 7 ){
					if(leaved_number == 0){dif =70 ; } //上班未打卡沒請假或處理
					else{
						if(leaved_number == 1){dif = 71;}//上班未打卡請病假
						if(leaved_number == 2){dif = 72;}//上班未打卡請事假
						if(leaved_number == 3){dif = 73;}//上班未打卡請公假
						if(leaved_number == 4){dif = 74;}//上班未打卡請補休假

					}
				}		
				if(att_status == 8 ){
					if(leaved_number == 0){dif =80 ; } //下班未打卡沒請假或處理
					else{
						if(leaved_number == 1){dif = 81;}//下班未打卡請病假
						if(leaved_number == 2){dif = 82;}//下班未打卡請事假
						if(leaved_number == 3){dif = 83;}//下班未打卡請公假
						if(leaved_number == 4){dif = 84;}//下班未打卡請補休假

					}
				}		
				if(att_status == 9 ){
					if(leaved_number == 0){dif =90 ; } //遲到+早退沒處理
					else{
						if(leaved_number == 1){dif = 91;}//遲到+早退請病假
						if(leaved_number == 2){dif = 92;}//遲到+早退請事假
						if(leaved_number == 3){dif = 93;}//遲到+早退請公假
						if(leaved_number == 4){dif = 94;}//遲到+早退請補休假

					}
				}	
				System.out.println("分類號 ="+dif );
				
				
				switch (dif){
				case 10:
					 diff = (int) (clock_in.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400));
					System.out.println(salary);
					break;
				case 11:
					 diff = (int) (clock_in.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400)*0.5);
						System.out.println(salary);
					//病扣半薪
					
					break;
				case 12:
					 diff = (int) (clock_in.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400));
						System.out.println(salary);
					// 事扣全薪
					break;
				case 13:
					
					// 公不扣
					break;
				case 14:
					 diff = (int) (clock_in.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 
					 
					// 補扣補休時數
					break;
				case 20:
					 diff = (int) (off_time_date.getTime()-clock_out.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400));
					System.out.println(salary);
					break;
				case 21:
					diff = (int) (off_time_date.getTime()-clock_out.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400)*0.5);
						System.out.println(salary);
					
					break;
				case 22:
					diff = (int) (off_time_date.getTime()-clock_out.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400));
						System.out.println(salary);
					break;
				case 23:
					
					
					
					
					break;
				case 24:
					diff = (int) (off_time_date.getTime()-clock_out.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));

					
					break;
				case 40:
					diff = (int) (off_time_date.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400));
						System.out.println(salary);
					break;
				case 41:
					diff = (int) (off_time_date.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400)*0.5);
						System.out.println(salary);
					break;
				case 42:
					diff = (int) (off_time_date.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400));
						System.out.println(salary);
					break;
				case 43:
					
					break;
				case 44:
					diff = (int) (off_time_date.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 
					 
					break;
				case 70:
					diff = (int) (off_time_date.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400));
						System.out.println(salary);
					break;
				case 71:
					diff = (int) (off_time_date.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400)*0.5);
						System.out.println(salary);
					break;
				case 72:
					diff = (int) (off_time_date.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400));
						System.out.println(salary);
					break;
				case 73:
					
					break;
				case 74:
					diff = (int) (off_time_date.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					break;
				case 80:
					diff = (int) (off_time_date.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400));
						System.out.println(salary);
					break;
				case 81:
					diff = (int) (off_time_date.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400)*0.5);
						System.out.println(salary);
					break;
				case 82:
					diff = (int) (off_time_date.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					 salary = salary-(diff_mins*(salary/14400));
						System.out.println(salary);
					break;
				case 83:
					
					break;
				case 84:
					diff = (int) (off_time_date.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_mins = (diff/(1000*60));
					break;
				case 90:
					diff = (int) (clock_in.getTime()-on_time_date.getTime());
						System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
						 diff_on = (diff/(1000*60));
						 
						 
					diff = (int) (off_time_date.getTime()-clock_out.getTime());
						System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
						 diff_off = (diff/(1000*60));	 
					     diff_mins = diff_on + diff_off;
						 salary = salary-(diff_mins*(salary/14400));
							System.out.println(salary);
					break;
				case 91:
					diff = (int) (clock_in.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_on = (diff/(1000*60));
					 
				diff = (int) (off_time_date.getTime()-clock_out.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_off = (diff/(1000*60));	 
				     diff_mins = diff_on + diff_off;
					 salary = salary-(diff_mins*(salary/14400)*0.5);
						System.out.println(salary);
					break;
				case 92:
					diff = (int) (clock_in.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_on = (diff/(1000*60));
					 
				diff = (int) (off_time_date.getTime()-clock_out.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_off = (diff/(1000*60));	 
				     diff_mins = diff_on + diff_off;
					 salary = salary-(diff_mins*(salary/14400));
						System.out.println(salary);
					break;
				case 93:
					
					
					break;
				case 94:
					diff = (int) (clock_in.getTime()-on_time_date.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_on = (diff/(1000*60));
					 
				diff = (int) (off_time_date.getTime()-clock_out.getTime());
					System.out.println("Difference is " + (diff/(1000*60)) + " mins."); 
					 diff_off = (diff/(1000*60));	 
				     diff_mins = diff_on + diff_off;
					break;

				}
				Connection con03= DriverManager.getConnection(url);
				PreparedStatement pstat03 = con03.prepareStatement(intosal);
				pstat03.setDouble(1, salary);
				pstat03.setString(2, user_id);
				pstat03.setInt(3, 201209);
				int w = pstat03.executeUpdate();
				System.out.println("改了改了"+w);		
			}
		
    	} catch (ClassNotFoundException e) {e.printStackTrace();} 
		catch (SQLException e) {e.printStackTrace();} catch (ParseException e) {e.printStackTrace();}	
	
		
	
	
	}
	
	

}
